 with  tmp_day as (
    select
         taking_agent_code
        ,taking_agent_name 
        ,sum(if(dt='{{ execution_date  | cst_ds }}',front_plan_zz,0))/   sum(if(dt='{{ execution_date  | cst_ds }}',front_number,0))                                  as d_gh_zz_num_1        --规划中转次数
        ,sum(if(dt='{{ execution_date  | cst_ds }}',front_actual_zz,0))/ sum(if(dt='{{ execution_date  | cst_ds }}',front_number,0))                                  as d_sj_zz_num_1        --实际中转次数
        
        ,sum(if(dt='{{ execution_date  | cst_ds }}',front_reback_zz,0))/ sum(if(dt='{{ execution_date  | cst_ds }}',front_reback,0))                                  as d_reback_zz_num_1    --退件中转次数
        ,sum(if(dt='{{ execution_date  | cst_ds }}',front_reback,0))                                                                              as d_reback_num_1       --退件件量
        ,sum(if(dt='{{ execution_date  | cst_ds }}',front_rational_zz,0))/ sum(if(dt='{{ execution_date  | cst_ds }}',front_rational,0))                              as d_rational_zz_num_1  --不合理中转次数
        ,sum(if(dt='{{ execution_date  | cst_ds }}',front_rational,0))                                                                            as d_rational_num_1     --不合理中转件量
        ,sum(if(dt='{{ execution_date  | cst_ds }}',front_is_wrong_zz,0))/ sum(if(dt='{{ execution_date  | cst_ds }}',front_is_wrong,0))                              as d_wrong_zz_num_1     --网点错发中转次数
        ,sum(if(dt='{{ execution_date  | cst_ds }}',front_is_wrong,0))                                                                            as d_wrong_num_1        --网点错发票数
        ,sum(if(dt='{{ execution_date  | cst_ds }}',front_bak_route_zz,0))/ sum(if(dt='{{ execution_date  | cst_ds }}',front_bak_route,0))                            as d_bak_zz_num_1       --备用路由中转次数
        ,sum(if(dt='{{ execution_date  | cst_ds }}',front_bak_route,0))                                                                           as d_bak_num_1          --备用路由中转件量
        ,sum(if(dt='{{ execution_date  | cst_ds }}',front_proble_zz,0)) / sum(if(dt='{{ execution_date  | cst_ds }}',front_proble,0))                                 as d_proble_zz_num_1    --问题件中转次数
        ,sum(if(dt='{{ execution_date  | cst_ds }}',front_proble,0))                                                                              as d_proble_num_1       --问题件件量
        
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_plan_zz,0))/   sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_number,0))        as d_gh_zz_num_2        --t-1规划中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_actual_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_number,0))        as d_sj_zz_num_2        --t-1实际中转次数
        
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_reback_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_reback,0))        as d_reback_zz_num_2    --t-1退件中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_reback,0))                                                                 as d_reback_num_2       --t-1退件件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_rational_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_rational,0))    as d_rational_zz_num_2  --t-1不合理中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_rational,0))                                                               as d_rational_num_2     --t-1不合理中转件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_is_wrong_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_is_wrong,0))    as d_wrong_zz_num_2     --t-1网点错发中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_is_wrong,0))                                                               as d_wrong_num_2        --t-1网点错发票数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_bak_route_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_bak_route,0))  as d_bak_zz_num_2       --t-1备用路由中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_bak_route,0))                                                              as d_bak_num_2          --t-1备用路由中转件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_proble_zz,0)) / sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_proble,0))       as d_proble_zz_num_2    --t-1问题件中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_proble,0))                                                                 as d_proble_num_2       --t-1问题件件量   
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-2),front_plan_zz,0))/   sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-2),front_number,0))        as d_gh_zz_num_3        --t-2规划中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-2),front_actual_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-2),front_number,0))        as d_sj_zz_num_3        --t-2实际中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-3),front_plan_zz,0))/   sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-3),front_number,0))        as d_gh_zz_num_4        --t-3规划中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-3),front_actual_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-3),front_number,0))        as d_sj_zz_num_4        --t-3实际中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-4),front_plan_zz,0))/   sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-4),front_number,0))        as d_gh_zz_num_5        --t-2规划中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-4),front_actual_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-4),front_number,0))        as d_sj_zz_num_5        --t-2实际中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-5),front_plan_zz,0))/   sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-5),front_number,0))        as d_gh_zz_num_6        --t-2规划中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-5),front_actual_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-5),front_number,0))        as d_sj_zz_num_6        --t-2实际中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-6),front_plan_zz,0))/   sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-6),front_number,0))        as d_gh_zz_num_7        --t-2规划中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-6),front_actual_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-6),front_number,0))        as d_sj_zz_num_7        --t-2实际中转次数
        ,0 as w_gh_zz_num_1
        ,0 as w_sj_zz_num_1
        ,0 as w_gh_zz_num_2
        ,0 as w_sj_zz_num_2
        ,0 as w_gh_zz_num_3
        ,0 as w_sj_zz_num_3
        ,0 as w_gh_zz_num_4
        ,0 as w_sj_zz_num_4
        ,0 as m_gh_zz_num_1
        ,0 as m_sj_zz_num_1
        ,0 as m_gh_zz_num_2
        ,0 as m_sj_zz_num_2
        ,0 as m_gh_zz_num_3
        ,0 as m_sj_zz_num_3
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-2),front_reback_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_reback,0))        as d_reback_zz_num_3    --t-1退件中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-2),front_reback,0))                                                                 as d_reback_num_3       --t-1退件件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-2),front_rational_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_rational,0))    as d_rational_zz_num_3  --t-1不合理中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-2),front_rational,0))                                                               as d_rational_num_3     --t-1不合理中转件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-2),front_is_wrong_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_is_wrong,0))    as d_wrong_zz_num_3     --t-1网点错发中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-2),front_is_wrong,0))                                                               as d_wrong_num_3        --t-1网点错发票数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-2),front_bak_route_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_bak_route,0))  as d_bak_zz_num_3       --t-1备用路由中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-2),front_bak_route,0))                                                              as d_bak_num_3          --t-1备用路由中转件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-2),front_proble_zz,0)) / sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_proble,0))       as d_proble_zz_num_3    --t-1问题件中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-2),front_proble,0))                                                                 as d_proble_num_3       --t-1问题件件量 
        
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-3),front_reback_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_reback,0))        as d_reback_zz_num_4    --t-1退件中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-3),front_reback,0))                                                                 as d_reback_num_4       --t-1退件件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-3),front_rational_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_rational,0))    as d_rational_zz_num_4  --t-1不合理中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-3),front_rational,0))                                                               as d_rational_num_4     --t-1不合理中转件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-3),front_is_wrong_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_is_wrong,0))    as d_wrong_zz_num_4     --t-1网点错发中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-3),front_is_wrong,0))                                                               as d_wrong_num_4        --t-1网点错发票数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-3),front_bak_route_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_bak_route,0))  as d_bak_zz_num_4       --t-1备用路由中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-3),front_bak_route,0))                                                              as d_bak_num_4          --t-1备用路由中转件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-3),front_proble_zz,0)) / sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_proble,0))       as d_proble_zz_num_4    --t-1问题件中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-3),front_proble,0))                                                                 as d_proble_num_4       --t-1问题件件量 
        
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-4),front_reback_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_reback,0))        as d_reback_zz_num_5    --t-1退件中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-4),front_reback,0))                                                                 as d_reback_num_5       --t-1退件件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-4),front_rational_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_rational,0))    as d_rational_zz_num_5  --t-1不合理中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-4),front_rational,0))                                                               as d_rational_num_5     --t-1不合理中转件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-4),front_is_wrong_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_is_wrong,0))    as d_wrong_zz_num_5     --t-1网点错发中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-4),front_is_wrong,0))                                                               as d_wrong_num_5        --t-1网点错发票数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-4),front_bak_route_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_bak_route,0))  as d_bak_zz_num_5       --t-1备用路由中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-4),front_bak_route,0))                                                              as d_bak_num_5          --t-1备用路由中转件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-4),front_proble_zz,0)) / sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_proble,0))       as d_proble_zz_num_5    --t-1问题件中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-4),front_proble,0))                                                                 as d_proble_num_5       --t-1问题件件量 
         
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-5),front_reback_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_reback,0))        as d_reback_zz_num_6    --t-1退件中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-5),front_reback,0))                                                                 as d_reback_num_6       --t-1退件件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-5),front_rational_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_rational,0))    as d_rational_zz_num_6  --t-1不合理中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-5),front_rational,0))                                                               as d_rational_num_6     --t-1不合理中转件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-5),front_is_wrong_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_is_wrong,0))    as d_wrong_zz_num_6     --t-1网点错发中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-5),front_is_wrong,0))                                                               as d_wrong_num_6        --t-1网点错发票数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-5),front_bak_route_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_bak_route,0))  as d_bak_zz_num_6       --t-1备用路由中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-5),front_bak_route,0))                                                              as d_bak_num_6          --t-1备用路由中转件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-5),front_proble_zz,0)) / sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_proble,0))       as d_proble_zz_num_6    --t-1问题件中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-5),front_proble,0))                                                                 as d_proble_num_6       --t-1问题件件量 
         
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-6),front_reback_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_reback,0))        as d_reback_zz_num_7    --t-1退件中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-6),front_reback,0))                                                                 as d_reback_num_7       --t-1退件件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-6),front_rational_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_rational,0))    as d_rational_zz_num_7  --t-1不合理中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-6),front_rational,0))                                                               as d_rational_num_7     --t-1不合理中转件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-6),front_is_wrong_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_is_wrong,0))    as d_wrong_zz_num_7     --t-1网点错发中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-6),front_is_wrong,0))                                                               as d_wrong_num_7        --t-1网点错发票数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-6),front_bak_route_zz,0))/ sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_bak_route,0))  as d_bak_zz_num_7       --t-1备用路由中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-6),front_bak_route,0))                                                              as d_bak_num_7          --t-1备用路由中转件量
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-6),front_proble_zz,0)) / sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-1),front_proble,0))       as d_proble_zz_num_7    --t-1问题件中转次数
        ,sum(if(dt=date_add('{{ execution_date  | cst_ds }}',-6),front_proble,0))                                                                 as d_proble_num_7       --t-1问题件件量 

        from jms_dm.dm_all_transfer_count_site_dt t
    where t.dt>=date_add('{{ execution_date  | cst_ds }}',-7) and t.dt<='{{ execution_date  | cst_ds }}'
    group by  taking_agent_code,taking_agent_name
    )
    ,tmp_week as (
        select
            t.taking_agent_code
            ,taking_agent_name as taking_agent_name
            ,0 as d_gh_zz_num_1
            ,0 as d_sj_zz_num_1
            ,0 as d_reback_zz_num_1
            ,0 as d_reback_num_1
            ,0 as d_rational_zz_num_1
            ,0 as d_rational_num_1
            ,0 as d_wrong_zz_num_1
            ,0 as d_wrong_num_1
            ,0 as d_bak_zz_num_1
            ,0 as d_bak_num_1
            ,0 as d_proble_zz_num_1
            ,0 as d_proble_num_1
            ,0 as d_gh_zz_num_2
            ,0 as d_sj_zz_num_2
            ,0 as d_reback_zz_num_2
            ,0 as d_reback_num_2
            ,0 as d_rational_zz_num_2
            ,0 as d_rational_num_2
            ,0 as d_wrong_zz_num_2
            ,0 as d_wrong_num_2
            ,0 as d_bak_zz_num_2
            ,0 as d_bak_num_2
            ,0 as d_proble_zz_num_2
            ,0 as d_proble_num_2
            ,0 as d_gh_zz_num_3
            ,0 as d_sj_zz_num_3
            ,0 as d_gh_zz_num_4
            ,0 as d_sj_zz_num_4
            ,0 as d_gh_zz_num_5
            ,0 as d_sj_zz_num_5
            ,0 as d_gh_zz_num_6
            ,0 as d_sj_zz_num_6
            ,0 as d_gh_zz_num_7
            ,0 as d_sj_zz_num_7
            ,max(if(rn=1,gh_zz_num,0)) as w_gh_zz_num_1
            ,max(if(rn=1,sj_zz_num,0)) as w_sj_zz_num_1
            ,max(if(rn=2,gh_zz_num,0)) as w_gh_zz_num_2
            ,max(if(rn=2,sj_zz_num,0)) as w_sj_zz_num_2
            ,max(if(rn=3,gh_zz_num,0)) as w_gh_zz_num_3
            ,max(if(rn=3,sj_zz_num,0)) as w_sj_zz_num_3
            ,max(if(rn=4,gh_zz_num,0)) as w_gh_zz_num_4
            ,max(if(rn=4,sj_zz_num,0)) as w_sj_zz_num_4
            ,0 as m_gh_zz_num_1
            ,0 as m_sj_zz_num_1
            ,0 as m_gh_zz_num_2
            ,0 as m_sj_zz_num_2
            ,0 as m_gh_zz_num_3
            ,0 as m_sj_zz_num_3
        ,0 as d_reback_zz_num_3    --t-1退件中转次数
        ,0 as d_reback_num_3       --t-1退件件量
        ,0 as d_rational_zz_num_3  --t-1不合理中转次数
        ,0 as d_rational_num_3     --t-1不合理中转件量
        ,0 as d_wrong_zz_num_3     --t-1网点错发中转次数
        ,0 as d_wrong_num_3        --t-1网点错发票数
        ,0 as d_bak_zz_num_3       --t-1备用路由中转次数
        ,0 as d_bak_num_3          --t-1备用路由中转件量
        ,0 as d_proble_zz_num_3    --t-1问题件中转次数
        ,0 as d_proble_num_3       --t-1问题件件量 
        
        ,0 as d_reback_zz_num_4    --t-1退件中转次数
        ,0 as d_reback_num_4       --t-1退件件量
        ,0 as d_rational_zz_num_4  --t-1不合理中转次数
        ,0 as d_rational_num_4     --t-1不合理中转件量
        ,0 as d_wrong_zz_num_4     --t-1网点错发中转次数
        ,0 as d_wrong_num_4        --t-1网点错发票数
        ,0 as d_bak_zz_num_4       --t-1备用路由中转次数
        ,0 as d_bak_num_4          --t-1备用路由中转件量
        ,0 as d_proble_zz_num_4    --t-1问题件中转次数
        ,0 as d_proble_num_4       --t-1问题件件量 
        
        ,0 as d_reback_zz_num_5    --t-1退件中转次数
        ,0 as d_reback_num_5       --t-1退件件量
        ,0 as d_rational_zz_num_5  --t-1不合理中转次数
        ,0 as d_rational_num_5     --t-1不合理中转件量
        ,0 as d_wrong_zz_num_5     --t-1网点错发中转次数
        ,0 as d_wrong_num_5        --t-1网点错发票数
        ,0 as d_bak_zz_num_5       --t-1备用路由中转次数
        ,0 as d_bak_num_5          --t-1备用路由中转件量
        ,0 as d_proble_zz_num_5    --t-1问题件中转次数
        ,0 as d_proble_num_5       --t-1问题件件量 
         
        ,0 as d_reback_zz_num_6    --t-1退件中转次数
        ,0 as d_reback_num_6       --t-1退件件量
        ,0 as d_rational_zz_num_6  --t-1不合理中转次数
        ,0 as d_rational_num_6     --t-1不合理中转件量
        ,0 as d_wrong_zz_num_6     --t-1网点错发中转次数
        ,0 as d_wrong_num_6        --t-1网点错发票数
        ,0 as d_bak_zz_num_6       --t-1备用路由中转次数
        ,0 as d_bak_num_6          --t-1备用路由中转件量
        ,0 as d_proble_zz_num_6    --t-1问题件中转次数
        ,0 as d_proble_num_6       --t-1问题件件量 
         
        ,0 as d_reback_zz_num_7    --t-1退件中转次数
        ,0 as d_reback_num_7       --t-1退件件量
        ,0 as d_rational_zz_num_7  --t-1不合理中转次数
        ,0 as d_rational_num_7     --t-1不合理中转件量
        ,0 as d_wrong_zz_num_7     --t-1网点错发中转次数
        ,0 as d_wrong_num_7        --t-1网点错发票数
        ,0 as d_bak_zz_num_7       --t-1备用路由中转次数
        ,0 as d_bak_num_7          --t-1备用路由中转件量
        ,0 as d_proble_zz_num_7    --t-1问题件中转次数
        ,0 as d_proble_num_7       --t-1问题件中转次数
        from (
                 select
                      taking_agent_code
                     ,taking_agent_name     
                    ,t1.week_id
                    ,sum(front_plan_zz)/   sum(front_number)        as gh_zz_num        -- 规划中转次数
                    ,sum(front_actual_zz)/ sum(front_number)        as sj_zz_num        -- 实际中转次数
                    ,dense_rank()over(order by week_id desc )  as rn
                from jms_dm.dm_all_transfer_count_site_dt t
                left join jms_dim.dim_date_info_base t1 on t.dt=t1.date_mid_desc
                where t.dt>=date_add('{{ execution_date  | cst_ds }}',-31) and t.dt<='{{ execution_date  | cst_ds }}'
                group by  taking_agent_code,t1.week_id,taking_agent_name
            ) t  where t.rn<=4 group by  taking_agent_code,taking_agent_name
    )
    ,tmp_month as (
        select
            t.taking_agent_code
            ,t.taking_agent_name
            ,0 as d_gh_zz_num_1
            ,0 as d_sj_zz_num_1
            ,0 as d_reback_zz_num_1
            ,0 as d_reback_num_1
            ,0 as d_rational_zz_num_1
            ,0 as d_rational_num_1
            ,0 as d_wrong_zz_num_1
            ,0 as d_wrong_num_1
            ,0 as d_bak_zz_num_1
            ,0 as d_bak_num_1
            ,0 as d_proble_zz_num_1
            ,0 as d_proble_num_1
            ,0 as d_gh_zz_num_2
            ,0 as d_sj_zz_num_2
            ,0 as d_reback_zz_num_2
            ,0 as d_reback_num_2
            ,0 as d_rational_zz_num_2
            ,0 as d_rational_num_2
            ,0 as d_wrong_zz_num_2
            ,0 as d_wrong_num_2
            ,0 as d_bak_zz_num_2
            ,0 as d_bak_num_2
            ,0 as d_proble_zz_num_2
            ,0 as d_proble_num_2
            ,0 as d_gh_zz_num_3
            ,0 as d_sj_zz_num_3
            ,0 as d_gh_zz_num_4
            ,0 as d_sj_zz_num_4
            ,0 as d_gh_zz_num_5
            ,0 as d_sj_zz_num_5
            ,0 as d_gh_zz_num_6
            ,0 as d_sj_zz_num_6
            ,0 as d_gh_zz_num_7
            ,0 as d_sj_zz_num_7
            ,0 as w_gh_zz_num_1
            ,0 as w_sj_zz_num_1
            ,0 as w_gh_zz_num_2
            ,0 as w_sj_zz_num_2
            ,0 as w_gh_zz_num_3
            ,0 as w_sj_zz_num_3
            ,0 as w_gh_zz_num_4
            ,0 as w_sj_zz_num_4
            ,max(if(rn=1,gh_zz_num,0)) as m_gh_zz_num_1
            ,max(if(rn=1,sj_zz_num,0)) as m_sj_zz_num_1
            ,max(if(rn=2,gh_zz_num,0)) as m_gh_zz_num_2
            ,max(if(rn=2,sj_zz_num,0)) as m_sj_zz_num_2
            ,max(if(rn=3,gh_zz_num,0)) as m_gh_zz_num_3
            ,max(if(rn=3,sj_zz_num,0)) as m_sj_zz_num_3
        ,0 as d_reback_zz_num_3    --t-3退件中转次数
        ,0 as d_reback_num_3       --t-3退件件量
        ,0 as d_rational_zz_num_3  --t-3不合理中转次数
        ,0 as d_rational_num_3     --t-3不合理中转件量
        ,0 as d_wrong_zz_num_3     --t-3网点错发中转次数
        ,0 as d_wrong_num_3        --t-3网点错发票数
        ,0 as d_bak_zz_num_3       --t-3备用路由中转次数
        ,0 as d_bak_num_3          --t-3备用路由中转件量
        ,0 as d_proble_zz_num_3    --t-3问题件中转次数
        ,0 as d_proble_num_3       --t-3问题件件量 
        
        ,0 as d_reback_zz_num_4    --t-4退件中转次数
        ,0 as d_reback_num_4       --t-4退件件量
        ,0 as d_rational_zz_num_4  --t-4不合理中转次数
        ,0 as d_rational_num_4     --t-4不合理中转件量
        ,0 as d_wrong_zz_num_4     --t-4网点错发中转次数
        ,0 as d_wrong_num_4        --t-4网点错发票数
        ,0 as d_bak_zz_num_4       --t-4备用路由中转次数
        ,0 as d_bak_num_4          --t-4备用路由中转件量
        ,0 as d_proble_zz_num_4    --t-4问题件中转次数
        ,0 as d_proble_num_4       --t-4问题件件量 
        
        ,0 as d_reback_zz_num_5    --t-5退件中转次数
        ,0 as d_reback_num_5       --t-5退件件量
        ,0 as d_rational_zz_num_5  --t-5不合理中转次数
        ,0 as d_rational_num_5     --t-5不合理中转件量
        ,0 as d_wrong_zz_num_5     --t-5网点错发中转次数
        ,0 as d_wrong_num_5        --t-5网点错发票数
        ,0 as d_bak_zz_num_5       --t-5备用路由中转次数
        ,0 as d_bak_num_5          --t-5备用路由中转件量
        ,0 as d_proble_zz_num_5    --t-5问题件中转次数
        ,0 as d_proble_num_5       --t-5问题件件量 
         
        ,0 as d_reback_zz_num_6    --t-6退件中转次数
        ,0 as d_reback_num_6       --t-6退件件量
        ,0 as d_rational_zz_num_6  --t-6不合理中转次数
        ,0 as d_rational_num_6     --t-6不合理中转件量
        ,0 as d_wrong_zz_num_6     --t-6网点错发中转次数
        ,0 as d_wrong_num_6        --t-6网点错发票数
        ,0 as d_bak_zz_num_6       --t-6备用路由中转次数
        ,0 as d_bak_num_6          --t-6备用路由中转件量
        ,0 as d_proble_zz_num_6    --t-6问题件中转次数
        ,0 as d_proble_num_6       --t-6问题件件量 
         
        ,0 as d_reback_zz_num_7    --t-7退件中转次数
        ,0 as d_reback_num_7       --t-7退件件量
        ,0 as d_rational_zz_num_7  --t-7不合理中转次数
        ,0 as d_rational_num_7     --t-7不合理中转件量
        ,0 as d_wrong_zz_num_7     --t-7网点错发中转次数
        ,0 as d_wrong_num_7        --t-7网点错发票数
        ,0 as d_bak_zz_num_7       --t-7备用路由中转次数
        ,0 as d_bak_num_7          --t-7备用路由中转件量
        ,0 as d_proble_zz_num_7    --t-7问题件中转次数
        ,0 as d_proble_num_7       --t-7问题件件量 
        from (
                 select
                      taking_agent_code
                     ,taking_agent_name
                    ,t1.month_id
                    ,sum(front_plan_zz)/   sum(front_number)        as gh_zz_num        -- 规划中转次数
                    ,sum(front_actual_zz)/ sum(front_number)        as sj_zz_num        -- 实际中转次数
                    ,dense_rank()over(order by month_id desc )  as rn
                from jms_dm.dm_all_transfer_count_site_dt t
                left join jms_dim.dim_date_info_base t1 on t.dt=t1.date_mid_desc
                where t.dt>=date_add('{{ execution_date  | cst_ds }}',-95) and t.dt<='{{ execution_date  | cst_ds }}'
                group by  taking_agent_code,t1.month_id,taking_agent_name
            ) t  where t.rn<=3 group by taking_agent_code,taking_agent_name
    )
insert overwrite table jms_dm.dm_all_transfer_count_robot_sum_dt partition (dt)
select 
     t.taking_agent_code                                              --代理区code
    ,taking_agent_name as taking_agent_name                      --代理区名称
    ,sum(t.d_gh_zz_num_1)       as d_gh_zz_num_1                      --日，t-1,规划中转次数
    ,sum(t.d_sj_zz_num_1)       as d_sj_zz_num_1                      --日，t-1,实际中转次数
    ,sum(t.d_reback_zz_num_1)   as d_reback_zz_num_1                  --日，t-1,退件中转次数
    ,sum(t.d_reback_num_1)      as d_reback_num_1                     --日，t-1,退件件量
    ,sum(t.d_rational_zz_num_1) as d_rational_zz_num_1                --日，t-1,不合理中转次数
    ,sum(t.d_rational_num_1)    as d_rational_num_1                   --日，t-1,不合理中转件量
    ,sum(t.d_wrong_zz_num_1)    as d_wrong_zz_num_1                   --日，t-1,网点错发中转次数
    ,sum(t.d_wrong_num_1)       as d_wrong_num_1                      --日，t-1,网点错发票数
    ,sum(t.d_bak_zz_num_1)      as d_bak_zz_num_1                     --日，t-1,备用路由中转次数
    ,sum(t.d_bak_num_1)         as d_bak_num_1                        --日，t-1,备用路由中转件量
    ,sum(t.d_proble_zz_num_1)   as d_proble_zz_num_1                  --日，t-1,问题件中转次数
    ,sum(t.d_proble_num_1)      as d_proble_num_1                     --日，t-1,问题件件量
    ,sum(t.d_gh_zz_num_2)       as d_gh_zz_num_2                      --日，t-2,规划中转次数
    ,sum(t.d_sj_zz_num_2)       as d_sj_zz_num_2                      --日，t-2,实际中转次数
    ,sum(t.d_reback_zz_num_2)   as d_reback_zz_num_2                  --日，t-2,退件中转次数
    ,sum(t.d_reback_num_2)      as d_reback_num_2                     --日，t-2,退件件量
    ,sum(t.d_rational_zz_num_2) as d_rational_zz_num_2                --日，t-2,不合理中转次数
    ,sum(t.d_rational_num_2)    as d_rational_num_2                   --日，t-2,不合理中转件量
    ,sum(t.d_wrong_zz_num_2)    as d_wrong_zz_num_2                   --日，t-2,网点错发中转次数
    ,sum(t.d_wrong_num_2)       as d_wrong_num_2                      --日，t-2,网点错发票数
    ,sum(t.d_bak_zz_num_2)      as d_bak_zz_num_2                     --日，t-2,备用路由中转次数
    ,sum(t.d_bak_num_2)         as d_bak_num_2                        --日，t-2,备用路由中转件量
    ,sum(t.d_proble_zz_num_2)   as d_proble_zz_num_2                  --日，t-2,问题件中转次数
    ,sum(t.d_proble_num_2)      as d_proble_num_2                     --日，t-2,问题件件量
    ,sum(t.d_gh_zz_num_3)       as d_gh_zz_num_3                      --日，t-3,规划中转次数
    ,sum(t.d_sj_zz_num_3)       as d_sj_zz_num_3                      --日，t-3,实际中转次数
    ,sum(t.d_gh_zz_num_4)       as d_gh_zz_num_4                      --日，t-4,规划中转次数
    ,sum(t.d_sj_zz_num_4)       as d_sj_zz_num_4                      --日，t-4,实际中转次数
    ,sum(t.d_gh_zz_num_5)       as d_gh_zz_num_5                      --日，t-5,规划中转次数
    ,sum(t.d_sj_zz_num_5)       as d_sj_zz_num_5                      --日，t-5,实际中转次数
    ,sum(t.d_gh_zz_num_6)       as d_gh_zz_num_6                      --日，t-6,规划中转次数
    ,sum(t.d_sj_zz_num_6)       as d_sj_zz_num_6                      --日，t-6,实际中转次数
    ,sum(t.d_gh_zz_num_7)       as d_gh_zz_num_7                      --日，t-7,规划中转次数
    ,sum(t.d_sj_zz_num_7)       as d_sj_zz_num_7                      --日，t-7,实际中转次数
    ,sum(t.w_gh_zz_num_1)       as w_gh_zz_num_1                      --周，t-1,规划中转次数
    ,sum(t.w_sj_zz_num_1)       as w_sj_zz_num_1                      --周，t-1,实际中转次数
    ,sum(t.w_gh_zz_num_2)       as w_gh_zz_num_2                      --周，t-2,规划中转次数
    ,sum(t.w_sj_zz_num_2)       as w_sj_zz_num_2                      --周，t-2,实际中转次数
    ,sum(t.w_gh_zz_num_3)       as w_gh_zz_num_3                      --周，t-3,规划中转次数
    ,sum(t.w_sj_zz_num_3)       as w_sj_zz_num_3                      --周，t-3,实际中转次数
    ,sum(t.w_gh_zz_num_4)       as w_gh_zz_num_4                      --周，t-4,规划中转次数
    ,sum(t.w_sj_zz_num_4)       as w_sj_zz_num_4                      --周，t-4,实际中转次数
    ,sum(t.m_gh_zz_num_1)       as m_gh_zz_num_1                      --月，t-1,规划中转次数
    ,sum(t.m_sj_zz_num_1)       as m_sj_zz_num_1                      --月，t-1,实际中转次数
    ,sum(t.m_gh_zz_num_2)       as m_gh_zz_num_2                      --月，t-2,规划中转次数
    ,sum(t.m_sj_zz_num_2)       as m_sj_zz_num_2                      --月，t-2,实际中转次数
    ,sum(t.m_gh_zz_num_3)       as m_gh_zz_num_3                      --月，t-3,规划中转次数
    ,sum(t.m_sj_zz_num_3)       as m_sj_zz_num_3                      --月，t-3,实际中转次数
    ,sum(t.d_reback_zz_num_3) as d_reback_zz_num_3    --t-3退件中转次数
    ,sum(t.d_reback_num_3) as d_reback_num_3       --t-3退件件量
    ,sum(t.d_rational_zz_num_3) as d_rational_zz_num_3  --t-3不合理中转次数
    ,sum(t.d_rational_num_3) as d_rational_num_3     --t-3不合理中转件量
    ,sum(t.d_wrong_zz_num_3) as d_wrong_zz_num_3     --t-3网点错发中转次数
    ,sum(t.d_wrong_num_3) as d_wrong_num_3        --t-3网点错发票数
    ,sum(t.d_bak_zz_num_3) as d_bak_zz_num_3       --t-3备用路由中转次数
    ,sum(t.d_bak_num_3) as d_bak_num_3          --t-3备用路由中转件量
    ,sum(t.d_proble_zz_num_3) as d_proble_zz_num_3    --t-3问题件中转次数
    ,sum(t.d_proble_num_3) as d_proble_num_3       --t-3问题件件量         
    ,sum(t.d_reback_zz_num_4) as d_reback_zz_num_4    --t-4退件中转次数
    ,sum(t.d_reback_num_4) as d_reback_num_4       --t-4退件件量
    ,sum(t.d_rational_zz_num_4) as d_rational_zz_num_4  --t-4不合理中转次数
    ,sum(t.d_rational_num_4) as d_rational_num_4     --t-4不合理中转件量
    ,sum(t.d_wrong_zz_num_4) as d_wrong_zz_num_4     --t-4网点错发中转次数
    ,sum(t.d_wrong_num_4) as d_wrong_num_4        --t-4网点错发票数
    ,sum(t.d_bak_zz_num_4) as d_bak_zz_num_4       --t-4备用路由中转次数
    ,sum(t.d_bak_num_4) as d_bak_num_4          --t-4备用路由中转件量
    ,sum(t.d_proble_zz_num_4) as d_proble_zz_num_4    --t-4问题件中转次数
    ,sum(t.d_proble_num_4) as d_proble_num_4       --t-4问题件件量     
    ,sum(t.d_reback_zz_num_5) as d_reback_zz_num_5    --t-5退件中转次数
    ,sum(t.d_reback_num_5) as d_reback_num_5       --t-5退件件量
    ,sum(t.d_rational_zz_num_5) as d_rational_zz_num_5  --t-5不合理中转次数
    ,sum(t.d_rational_num_5) as d_rational_num_5     --t-5不合理中转件量
    ,sum(t.d_wrong_zz_num_5) as d_wrong_zz_num_5     --t-5网点错发中转次数
    ,sum(t.d_wrong_num_5) as d_wrong_num_5        --t-5网点错发票数
    ,sum(t.d_bak_zz_num_5) as d_bak_zz_num_5       --t-5备用路由中转次数
    ,sum(t.d_bak_num_5) as d_bak_num_5          --t-5备用路由中转件量
    ,sum(t.d_proble_zz_num_5) as d_proble_zz_num_5    --t-5问题件中转次数
    ,sum(t.d_proble_num_5) as d_proble_num_5       --t-5问题件件量       
    ,sum(t.d_reback_zz_num_6) as d_reback_zz_num_6    --t-6退件中转次数
    ,sum(t.d_reback_num_6) as d_reback_num_6       --t-6退件件量
    ,sum(t.d_rational_zz_num_6) as d_rational_zz_num_6  --t-6不合理中转次数
    ,sum(t.d_rational_num_6) as d_rational_num_6     --t-6不合理中转件量
    ,sum(t.d_wrong_zz_num_6) as d_wrong_zz_num_6     --t-6网点错发中转次数
    ,sum(t.d_wrong_num_6) as d_wrong_num_6        --t-6网点错发票数
    ,sum(t.d_bak_zz_num_6) as d_bak_zz_num_6       --t-6备用路由中转次数
    ,sum(t.d_bak_num_6) as d_bak_num_6          --t-6备用路由中转件量
    ,sum(t.d_proble_zz_num_6) as d_proble_zz_num_6    --t-6问题件中转次数
    ,sum(t.d_proble_num_6) as d_proble_num_6       --t-6问题件件量        
    ,sum(t.d_reback_zz_num_7) as d_reback_zz_num_7    --t-7退件中转次数
    ,sum(t.d_reback_num_7) as d_reback_num_7       --t-7退件件量
    ,sum(t.d_rational_zz_num_7) as d_rational_zz_num_7  --t-7不合理中转次数
    ,sum(t.d_rational_num_7) as d_rational_num_7     --t-7不合理中转件量
    ,sum(t.d_wrong_zz_num_7) as d_wrong_zz_num_7     --t-7网点错发中转次数
    ,sum(t.d_wrong_num_7) as d_wrong_num_7        --t-7网点错发票数
    ,sum(t.d_bak_zz_num_7) as d_bak_zz_num_7       --t-7备用路由中转次数
    ,sum(t.d_bak_num_7) as d_bak_num_7          --t-7备用路由中转件量
    ,sum(t.d_proble_zz_num_7) as d_proble_zz_num_7    --t-7问题件中转次数
    ,sum(t.d_proble_num_7) as d_proble_num_7       --t-7问题件件量 
    ,'{{ execution_date  | cst_ds }}' as date_time
    ,'{{ execution_date  | cst_ds }}' as dt
from (
    select * from tmp_day
    union all
    select * from tmp_week
    union all
    select * from tmp_month
) t
where taking_agent_code is not null 
group by  taking_agent_code,taking_agent_name
distribute by pmod(hash(rand()), 2)
;

